﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using BusinessLogic.BusinessEntity.Interfaces;
using DataControl.Exceptions;
using NHibernate;
using DataControl.Entities;
using System.Diagnostics;

namespace DataControl
{
	public class DataControl
	{
		private readonly ISession session;

		public DataControl(ISession session)
		{
			this.session = session;
		}
		
		/// <summary>
		///  Использовать сразу при запуске приложения, для первого "долгого" коннекта
		/// </summary>
		public static bool Connect()
		{
			var session = SessionManager.GetSession();
			if (session != null)
			{
				session.Close();
				return true;
			}
			
			throw new ConnectionException("It was impossible to open a connection to DB.");
		}

		#region Специлизированные команды

		/// <summary>
		/// Проверка существования счета
		/// Номер счета - только число, 
		/// другое - выкинет ошибку))
		/// </summary>
		public bool IsAccountExist(string accountCode)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format("select * from bank.account where account_num = {0}", accountCode);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Account)).List<Account>() as List<Account>;

			               		if ((list == null) || (list.Count == 0)) return false;
			               		return true;
			               	});
		}

		/// <summary>
		/// Получение аккуанта
		/// по его номеру
		/// </summary>
		public Account GetAccountByNumber(string accountCode)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format("select * from bank.account where account_num = {0}", accountCode);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Account)).List<Account>() as List<Account>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0];
			               	});
		}

		/// <summary>
		/// Получение аккуанта по его ID
		/// </summary>
		public Account GetAccountById(int accountId)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format("select * from bank.account where id_account = {0}", accountId);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Account)).List<Account>() as List<Account>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0];
			               	});
		}

		/// <summary>
		/// Обновление баланса указанного счета
		/// </summary>
		public void UpdateBalance(int accountId, decimal balance)
		{
			var acc = GetAccountById(accountId);
			if (acc == null) return;

			acc.Balance = balance;
			Edit(acc);
		}


		/// <summary>
		/// Получение значения конверсии валюты
		/// </summary>
		public decimal? GetCurrencyConversion(string currencyCode1, string currencyCode2)
		{
			return Execute(() =>
			               	{
			               		var query =
			               			string.Format(
			               				@"select *
                                            from
                                            (
                                            select ce.*
                                            from bank.cur_exchange ce
                                                 inner join
                                                 bank.currency cur1
                                                 on ce.id_currency_1 = cur1.id_currency
                                                    inner join bank.currency cur2
                                                    on ce.id_currency_2 = cur2.id_currency
                                            where cur1.code = '{0}'
                                              and cur2.code = '{1}'
                                            order by ce.act_date desc
                                            )
                                            where rownum = 1",
			               				currencyCode1, currencyCode2);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Cur_exchange)).List<Cur_exchange>() as
			               			List<Cur_exchange>;

			               		if ((list == null) || (list.Count == 0)) return (decimal?)null;
			               		return list[0].Price;
			               	});
		}


		/// <summary>
		/// Проверка существования клиента по ID
		/// </summary>
		public bool ClientExists(int clientId)
		{
			return Execute(() =>
           	{
           		var query = string.Format("select * from bank.client where id_client = {0}", clientId);

           		var list = session.CreateSQLQuery(query).AddEntity(typeof (User)).List<User>() as List<User>;

				return (list != null) && (list.Count != 0);
           	});
		}
		

		public bool ClientExists(string login)
		{
			return Execute(() =>
			{
				var query = string.Format("select * from bank.client where Login = \'{0}\'", login);

				var list = session.CreateSQLQuery(query).AddEntity(typeof(User)).List<User>() as List<User>;

				return (list != null) && (list.Count != 0);
			});
		}

		/// <summary>
		/// Получение клиента по номеру его счета
		/// </summary>
		public User GetClientByAccount(string accountCode)
		{
			return Execute(() =>
			               	{
			               		var query =
			               			string.Format(
			               				@"select cl.*
                                            from bank.client cl
                                                 inner join 
                                                 bank.account ac
                                                 on cl.id_client = ac.id_client
                                            where ac.account_num = {0}",
			               				accountCode);

			               		var list = session.CreateSQLQuery(query).AddEntity(typeof (User)).List<User>() as List<User>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0];
			               	});
		}

		/// <summary>
		/// Проверка существования валюты по коду
		/// </summary>
		public bool IsCurrencyExist(string currencyCode)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format("select * from bank.currency where code = '{0}'", currencyCode);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Currency)).List<Currency>() as List<Currency>;

			               		return (list != null) && (list.Count != 0);
			               	});
		}

		/// <summary>
		/// Получение валюты по ID
		/// </summary>
		public ICurrency GetCurrencyById(int currencyId)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.currency where id_currency = {0}", currencyId);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Currency)).List<Currency>() as List<Currency>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0];
			               	});
		}

		/// <summary>
		/// Получение IDRAStatus по тексту статуса 
		/// </summary>
		public int GetDocumentEnvelopeStatusByName(string status)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.document_envelope_status where name = '{0}'", status);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (document_envelope_status)).List<document_envelope_status>() as List<document_envelope_status>;

			               		if ((list == null) || (list.Count == 0)) return -1;
			               		return list[0].Id_ra_status;
			               	});
		}

		/// <summary>
		/// Получение публичного ключа клиента
		/// </summary>
		public string GetPublicKeyByClientId(int clientId)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.client where id_client = {0}", clientId);

			               		var list = session.CreateSQLQuery(query).AddEntity(typeof (User)).List<User>() as List<User>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0].PublicKey;
			               	});
		}


		/// <summary>
		/// Получение списка номеров RA клиента
		/// </summary>
		public IEnumerable<string> GetListRANumByClientId(int clientId)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.ra where id_acc_payer = {0}", clientId);

			               		var list = session.CreateSQLQuery(query).AddEntity(typeof (RA)).List<RA>() as List<RA>;

			               		if ((list == null) || (list.Count == 0)) return new List<string>();

			               		var lst = new List<string>();
			               		foreach (var l in list)
			               			lst.Add(l.Number);

			               		return lst;
			               	});
		}

		public IEnumerable<RA> GetReceivedAdvices(int receiverPartyId, DateTime startDate, DateTime endDate)
		{
			return Execute(() =>
			               	{
			               		var query =
			               			string.Format(
										@"
															SELECT ra.* from RA ra 
															JOIN Account ac ON ac.Id_account = ra.Id_acc_billto
															WHERE ac.Id_client = {0}
															AND ra.Act_date >= TO_DATE('{1}', 'DD.MM.YYYY HH24:MI:SS') 
															AND ra.Act_date <= TO_DATE('{2}', 'DD.MM.YYYY HH24:MI:SS')",
			               				receiverPartyId, startDate, endDate);

								var list = session.CreateSQLQuery(query).AddEntity(typeof(RA)).List<RA>() as List<RA>;

								if ((list == null) || (list.Count == 0)) return new List<RA>();

			               		return list;
			               	});
		}

		public IEnumerable<RA> GetSentAdvices(int senderId, DateTime startDate, DateTime endDate)
		{
			return Execute(() =>
			               	{
			               		var query =
			               			string.Format(
										@"
															SELECT ra.* from RA ra 
															JOIN Account ac ON ac.Id_account = ra.Id_acc_payer
															WHERE ac.Id_client = {0}
															AND ra.Act_date >= TO_DATE('{1}', 'DD.MM.YYYY HH24:MI:SS') 
															AND ra.Act_date <= TO_DATE('{2}', 'DD.MM.YYYY HH24:MI:SS')",
			               				senderId, startDate, endDate);

			               		var list = session.CreateSQLQuery(query).AddEntity(typeof (RA)).List<RA>() as List<RA>;

			               		if ((list == null) || (list.Count == 0)) return new List<RA>();

			               		return list;
			               	});
		}

		/// <summary>
		/// Получение ID роли по её имени
		/// </summary>
		/// <param name="name"></param>
		/// <returns></returns>
		public int GetRoleIdByRoleName(string name)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.user_role where name = '{0}'", name);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (User_role)).List<User_role>() as List<User_role>;

			               		if ((list == null) || (list.Count == 0)) return -1;
			               		return list[0].id_user_role;
			               	});
		}

		/// <summary>
		/// Получение списка ID ролей указаного пользователя
		/// </summary>
		/// <param name="clientId"></param>
		/// <returns></returns>
		public List<int> GetClientRoles(int clientId)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.bnk_user_role where id_bnk_user = {0}", clientId);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (Bnk_user_role)).List<Bnk_user_role>() as
			               			List<Bnk_user_role>;

			               		if ((list == null) || (list.Count == 0)) return new List<int>();

			               		var result = new List<int>();
			               		foreach (var item in list)
			               			result.Add(item.Id_user_role);

			               		return result;
			               	});
		}


		public List<int> GetRoles()
		{
			return new List<int> { 1, 2, 3 };
		}


		/// <summary>
		/// Получить ID клиента по логину и паролю,
		/// в случае ошибки или отсутствия пользователя - null
		/// </summary>
		public int? EnsureClientCredentials(string login, string password)
		{
			return Execute<int?>(() =>
			               	{
			               		var query = string.Format(@"select * from bank.Client where login = '{0}' and pass = '{1}'",
			               		                          login, password);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (User)).List<User>() as List<User>;

			               		if ((list == null) || (list.Count == 0)) return null;
			               		return list[0].ID;
			               	});
		}

		public int GetDocumentTypeIdByName(string docTypeName)
		{
			return Execute(() =>
			               	{
			               		var query = string.Format(@"select * from bank.document_envelope_type where envelope_type = '{0}'",
			               		                          docTypeName);

			               		var list =
			               			session.CreateSQLQuery(query).AddEntity(typeof (DocumentType)).List<DocumentType>()
			               			as List<DocumentType>;

			               		if ((list == null) || (list.Count == 0)) return -1;
			               		return list[0].DocumentTypeId;
			               	});
		}


		/// <summary>
		/// Зачистка базы
		/// </summary>
		public bool ClearBase()
		{
			return Execute(() =>
			{
				session.CreateSQLQuery("delete from bank.RA").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.BNK_USER_ROLE").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.ACCOUNT").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.CLIENT").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.CLIENT_RA_NUM").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.CUR_EXCHANGE").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.DOCUMENT_ENVELOPE").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.RATE").ExecuteUpdate();
				session.CreateSQLQuery("delete from bank.CURRENCY").ExecuteUpdate();
				session.Flush();
				return true;
			});
		}



		#endregion

		#region Универсальные команды

		public T LoadItemById<T>(int id)
		{
			return Execute(() =>
			               	{
			               		var result = session.Get<T>(id);
			               		return result;
			               	});
		}

		public List<T> GetAllItems<T>()
		{
			return Execute(() =>
			               	{
			               		var list = session.CreateCriteria(typeof (T)).List<T>() as List<T>;
								//var criteria = session.CreateCriteria(typeof (T));
								//criteria.Add(new NHibernate.Criterion.)

			               		if (list != null) return new List<T>(list);
			               		return new List<T>();
			               	});
		}

		public BindingList<T> GetFiltredItems<T>(string fieldName, object value)
		{
			return Execute(() =>
			               	{
			               		var typeName = typeof (T).Name;
			               		var query = string.Format("from {0} where {1} = {2}", typeName, fieldName, value);
			               		var list = session.CreateQuery(query).List<T>() as List<T>;

			               		if (list != null) return new BindingList<T>(list);
			               		return new BindingList<T>();
			               	});
		}

		public BindingList<T> GetFiltredItems<T>(string fieldName, IEnumerable values)
		{
			return Execute(() =>
			               	{
			               		var typeName = typeof (T).Name;
			               		string inText = string.Empty;
			               		foreach (var value in values)
			               			inText = string.Concat(inText, ",'", value.ToString(), "'");
			               		inText = inText.Trim(',');

			               		if (inText.Length == 0) return new BindingList<T>();

			               		string query = string.Format("from {0} where {1} in ({2})", typeName, fieldName, inText);

			               		var list = session.CreateQuery(query).List<T>() as List<T>;
			               		if (list != null) return new BindingList<T>(list);
			               		return new BindingList<T>();
			               	});
		}

		// Добавление новой записи
		public int Add<T>(T obj) where T : class, IIdentifiedEntity
		{
			return Execute(() =>
           		{
           			ClearSessionObject<T>(session, obj.ID);
					var id = (int) session.Save(obj);
           			session.Flush();
           			return id;
           		});
		}

		// Редактирование записи
		public void Edit<T>(T obj) where T : class, IIdentifiedEntity
		{
			Execute(() =>
           		{
					ClearSessionObject<T>(session, obj.ID);
           			session.Update(obj);
           			session.Flush();
           		});
		}

		// Удаление записи
		public void Delete<T>(T obj) where T : class, IIdentifiedEntity
		{
			Execute(() =>
       			{
					ClearSessionObject<T>(session, obj.ID);
   					session.Delete(obj);
   					session.Flush();
       			});
		}

		// Удаление записи
		public void Delete<T>(string fieldName, string value)
		{
			Execute(() =>
               	{
               		var typeName = typeof (T).Name;
               		string query = string.Format("delete from {0} where {1} = {2}", typeName, fieldName, value);
               		var result = session.CreateQuery(query).ExecuteUpdate();
					Debug.Assert(result >= 0);
               	});
		}

		public void Delete<T>(IList<KeyValuePair<string, string>> criterias)
		{
			Execute(() =>
				{
					var typeName = typeof(T).Name;
					var where = new StringBuilder();
					for(int i = 0; i < criterias.Count; i++)
					{
						where.Append(criterias[i].Key + "='" + criterias[i].Value + "'");
						if(i != (criterias.Count - 1))
						{
							where.Append(" AND ");
						}
					}
					string query = string.Format("delete from {0} where {1}", typeName, where);
					var result = session.CreateQuery(query).ExecuteUpdate();
					Debug.Assert(result >= 0);
				});
		}

		// Удаление записи
		public void Delete<T>(string fieldName, IEnumerable values)
		{
			Execute(() =>
               	{
               		var typeName = typeof (T).Name;
               		string inText = string.Empty;

					foreach (var value in values)
					{
						inText = string.Concat(inText, ",'", value.ToString(), "'");
					}

               		inText = inText.Trim(',');

               		//if (inText.Length == 0) return true;
					Debug.Assert(inText.Length > 0);

               		string query = string.Format("delete from {0} where {1} in ({2})", typeName, fieldName, inText);

               		var result = session.CreateQuery(query).ExecuteUpdate();
					Debug.Assert(result >= 0);
               	});
		}

		#endregion


		private static TResult Execute<TResult>(Func<TResult> func)
		{
			using (ISession session = SessionManager.GetSession())
			{
				if (session == null)
				{
					throw new Exceptions.SessionException("It was impossible to create a session within a connection");
				}

				try
				{
					return func();
				}
				catch (Exception ex)
				{
					throw new DataAccessException("Data query failed!", ex);
				}
			}
		}


		private static void Execute(Action func)
		{
			using (ISession session = SessionManager.GetSession())
			{
				if (session == null)
				{
					throw new Exceptions.SessionException("It was impossible to create a session within a connection");
				}

				try
				{
					func();
				}
				catch (Exception ex)
				{
					throw new DataAccessException("Data modification operation failed!", ex);
				}
			}
		}


		private static void ClearSessionObject<T>(ISession session, int id) where T : class, IIdentifiedEntity
		{
			var obj = session.Get<T>(id);

			if(obj != null)
			{
				session.Evict(obj);
			}
		}
	}
}